package com.config.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.config.dao.ShiftConfigDao;
import com.config.entity.ShiftConfig;
import com.base.dao.TemplateDaoImpl;
import com.base.utils.BeanToSqlutils;

@Repository(value="shiftConfigDao")
public class ShiftConfigDaoImpl extends TemplateDaoImpl<ShiftConfig,Integer>  implements ShiftConfigDao{
	@Override
	public int getTotal() {
		StringBuilder sql = new StringBuilder("select  count(*)  from   ");
		sql.append(BeanToSqlutils.getTableName(ShiftConfig.class));
		sql.append(" where 1=1"); 
		Map<String, Object> params = new HashMap<String, Object>();
	    return jt.queryForObject( sql.toString(), params,Integer.class);
	}

	/**
	 *  查询指定生产线下正在使用的班制
	 * @param lineId
	 * @param belongTime  年月日
	 * @return
	 */
	@Override
	public ShiftConfig findShiftsByLineId(int lineId,String belongTime) {
		StringBuffer sql = new StringBuffer(" select * from shiftConfig  where lineId=:lineId  ")
		                         .append(" and CONVERT(VARCHAR(10),effectiveStartTime,120)<=:belongTime")
		                         .append(" and CONVERT(VARCHAR(10),effectiveEndTime,120)>=:belongTime and isEffective='Y'");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("lineId", lineId);
		params.put("belongTime", belongTime);
		return jt.queryForObject(sql.toString(),params,
				ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}
	/**
	 * 查询指定班制下的所有班次信息
	 * @param fatherId
	 * @return
	 */
	@Override
	public List<ShiftConfig> findByShiftsId(int fatherId) {
		StringBuffer sql = new StringBuffer(" select * from shiftConfig  where fatherId=:fatherId order by startTime asc ");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("fatherId", fatherId);
		return jt.query(sql.toString(),params,
				ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}
	/**
	 * 根据时间查询当前使用的班次
	 * @param belongTime  年月日时分秒
	 * @param lineId
	 * @return
	 */
	@Override
	public ShiftConfig findShiftConfigByDate(int lineId, Date belongTime) {
		String time="";String belong="";String date="";
		try {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdt = new SimpleDateFormat("HH:mm:ss");
		date=sdf.format(belongTime);
		time=sdt.format(belongTime);
		belong="2016-01-01"+" "+time;
		//使用的班制
		ShiftConfig shift=this.findShiftsByLineId(lineId, sdf.format(belongTime));
		//该班制下所有班次
		if(shift!=null){
			List<ShiftConfig> list=this.findByShiftsId(shift.getId());
			if(list.size()!=0){
				Date biggerStartDate=list.get(0).getStartTime();
				for(int i=0;i<list.size();i++){
					if((i+1)<list.size()){
						if(list.get(i+1).getStartTime().before(biggerStartDate)){
							biggerStartDate=list.get(i+1).getStartTime();
						}
					}
				}
				if((sdf.parse(belong)).compareTo(biggerStartDate)<=0){
					belong="2016-01-02"+" "+time;
				}
		}
		}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		StringBuffer sql = new StringBuffer("select * from shiftConfig where fatherId=(select id from shiftConfig where lineId=:lineId ")
		                         .append(" and  CONVERT(VARCHAR(19),effectiveStartTime,120)<=:date  ")
		                         .append(" and  CONVERT(VARCHAR(19),effectiveEndTime,120)>=:date  and isEffective='Y')  ")
		                         .append("  and CONVERT(VARCHAR(19),startTime,120)<:belong  and CONVERT(VARCHAR(19),endTime,120)>=:belong ");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("lineId", lineId);
		params.put("date", date);
		params.put("belong", belong);
		return jt.queryForObject(sql.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}
	/**
	 * 查询生产线下所有的班制
	 * @param lineId
	 * @return
	 */
	@Override
	public List<ShiftConfig> findShifts(int lineId) {
		StringBuffer sql = new StringBuffer(" select * from shiftConfig  where lineId=:lineId  ");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("lineId", lineId);
		return jt.query(sql.toString(),params,
				ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}

	@Override
	public ShiftConfig findShiftsByTime(int lineId,String startTime,String endTime) {
		StringBuffer sql = new StringBuffer(" select * from shiftConfig  where lineId=:lineId  ")
        .append(" and CONVERT(VARCHAR(10),effectiveStartTime,120)<=:startTime")
        .append(" and CONVERT(VARCHAR(10),effectiveEndTime,120)>=:endTime");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("lineId", lineId);
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		return jt.queryForObject(sql.toString(),params,
		ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}
	
	@Override
	public String gettime(ShiftConfig shift, String belongDate) {
		String str="";
		SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
		SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd");
		try {
			String startTime="";String endTime="";
			Calendar start = Calendar.getInstance();
			Calendar end = (Calendar) start.clone();
			start.setTime(shift.getStartTime());
			end.setTime(shift.getEndTime());
			String startDate=sdft.format(start.getTime());
			String endDate=sdft.format(end.getTime());
			if(startDate.equals("2016-01-01")){
				//查询日班次开始时间
				  startTime=belongDate+" "+sdf.format(shift.getStartTime());
			}else{
				//结束时间跨天
				 startTime=sdft.format(new Date(sdft.parse(belongDate).getTime()+24*60*60*1000L))+" "+sdf.format(shift.getStartTime());
			}
			if(endDate.equals("2016-01-01")){
				 //查询日班次结束时间
				  endTime=belongDate+" "+sdf.format(shift.getEndTime());
			}else{
				//结束时间跨天
				 endTime=sdft.format(new Date(sdft.parse(belongDate).getTime()+24*60*60*1000L))+" "+sdf.format(shift.getEndTime());
			}
			str=startTime+"|"+endTime;
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return str;
	}

	@Override
	public List<ShiftConfig> findShiftConfigsByLine(int lineId) {
		StringBuffer sql = new StringBuffer(" select * from shiftConfig  where fatherId in(select id from shiftConfig b where b.lineId=:lineId)  ");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("lineId", lineId);
		return jt.query(sql.toString(),params,
				ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}
	 /**
	  * 查询生产线下不同时间存在的班制
	  * @param lineId
	  * @param belongTime
	  * @return
	  */
	@Override
	public ShiftConfig findShiftsByLine(int lineId, String belongTime) {
		StringBuffer sql = new StringBuffer(" select * from shiftConfig  where lineId=:lineId  ")
        .append(" and CONVERT(VARCHAR(10),effectiveStartTime,120)<=:belongTime")
        .append(" and CONVERT(VARCHAR(10),effectiveEndTime,120)>=:belongTime ");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("lineId", lineId);
		params.put("belongTime", belongTime);
		return jt.queryForObject(sql.toString(),params,
		ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}

	@Override
	public int countByFatherId(Integer fatherId) {
		StringBuffer sb = new StringBuffer("select count(*) from shiftConfig where 1=1");
		Map<String, Object> params=new HashMap<String, Object>();
		if( fatherId==null||fatherId.equals(null)){
			sb.append(" and fatherId is null");
		}else{
			sb.append(" and fatherId=:fatherId");
			params.put("fatherId", fatherId);
		}
		return jt.queryForObject(sb.toString(), params, Integer.class);
	
	}

	@Override
	public List<ShiftConfig> findByLineTreePage(Integer lineId, int start, int limit) {
		StringBuilder sb = new StringBuilder("select top "+limit+" * from (")
			.append("select row_number() over(order by id asc ) rowindex,* from ")
			.append("shiftConfig where 1=1");
		Map<String, Object> params=new HashMap<String, Object>();
		if(lineId!=null&&!lineId.equals("")){
			sb.append(" and lineId=:lineId");
			params.put("lineId", lineId);
		}
		sb.append(") u where u.rowindex>:start");
		params.put("start", (start-1)*limit);
		params.put("limit", limit);
		return jt.query(sb.toString(),params,
				ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}

	@Override
	public int countByLineTreePage(Integer lineId) {
		StringBuffer sb = new StringBuffer("select count(*) from shiftConfig where 1=1");
		Map<String, Object> params=new HashMap<String, Object>();
		if(lineId!=null&&!lineId.equals("")){
			sb.append(" and lineId=:lineId");
			params.put("lineId", lineId);
		}
		return jt.queryForObject(sb.toString(), params, Integer.class);
	}

	@Override
	public ShiftConfig getEffectiveOne(Integer lineId) {
		StringBuffer sql=new StringBuffer("select * from shiftConfig where lineId=:lineId and   isEffective='Y'");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("lineId", lineId);
		return jt.queryForObject(sql.toString(),params,
				ParameterizedBeanPropertyRowMapper.newInstance(ShiftConfig.class));
	}
}
